﻿Imports Conection
Imports System.Data.SqlClient

Public Class ClassificacaoFiscalDAL
#Region "Incluir"
    Public Function inserir(ByVal clf_descricao As String, ByVal sta_codigo As Integer, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "insert into Estoque.Gen_ClaFiscal(clf_descricao,sta_codigo) values (@descricao,@status)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar, 150)).Value = clf_descricao
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int, 1)).Value = sta_codigo
            codigo = objconexao.executacmdAI(cmd, "Gen_ClaFiscal")
            objfuncoes.gravaocorrencias("Lançamento de Classificação Fiscal. op: Inserção", codigo, 2, usuario)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Alterar"
    Public Function alterar(ByVal clf_descricao As String, ByVal sta_codigo As Integer, ByVal clf_codigo As Integer, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "update Estoque.Gen_ClaFiscal set clf_descricao=@descricao, sta_codigo=@status where clf_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar, 150)).Value = clf_descricao
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = clf_codigo
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int, 1)).Value = sta_codigo
            codigo = objconexao.executaCmd(cmd)
            objfuncoes.gravaocorrencias("Lançamento de Classificação Fiscal. op: Alteração", codigo, 2, usuario)
        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Function buscar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 1
                    'por codigo
                    cmd.CommandText = "select * from Estoque.Gen_ClaFiscal where  clf_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = descricao
                Case 2
                    'por descricao
                    cmd.CommandText = "select * from vw_EstClassificacaoFiscal where clf_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 3
                    'status
                    cmd.CommandText = "select * from vw_EstClassificacaoFiscal where sta_codigo=@codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case Else
                    'todos
                    cmd.CommandText = "select * from vw_EstClassificacaoFiscal"
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Verificar Relacionamentos"
    Public Function verificarelacionamento(codigo As Integer) As String
        Dim cmd As New SqlCommand
        Dim dt As New DataTable
        Dim ret As String
        Try
            ret = ""
            'verificar em peças
            cmd.CommandText = "select * from vw_EstPeca where clf_codigo in(select clf_codigo from vw_EstClassificacaoFiscal where sta_codigo in(1,2)) and clf_codigo=" & codigo
            dt = objconexao.executaConsulta(cmd)
            If (dt.Rows.Count > 0) Then
                ret = "Peças"
            End If
        Catch ex As Exception
            Throw New Exception("Erro ao realizar consulta.")
        End Try
        Return ret
    End Function
#End Region
End Class
